﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using E4RPPrintManager;
using E4RPTools;
using SalesmanCommissionReport.Controls;
using SalesmanCommissionReport.DataSets;

namespace SalesmanCommissionReport
{
    public class Controller
    {

        public string LogFile { get; set; }

        private SqlConnection _connection;

       
        private void FixConnection()
        {
            if (_connection.State == ConnectionState.Open)
                _connection.Close();
            _connection.Open();
        }

        private VendedorDataSet GetSalesmans()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetSalesmans(sqlTransaction);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(null, e, LogFile, "SalesmanCommissionReport", "Não foi possível ler a lista de vendedores.");
                return new VendedorDataSet();
            }
        }

        private void SetReportData(DateTime beginDate, DateTime endDate, string salesman)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                Database.SetupTemporaryTables(sqlTransaction);
                sqlTransaction.Commit();
                
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.PublishAndShowErrorMessage(null, e, LogFile, "SalesmanCommissionReport", "Não foi possível processar o relatório.");                
            }
            

            //SELECT LD.Vendedor, V.Nome,  (CASE WHEN CD.TipoEntidade = 'C' THEN (SELECT TOP 1 Nome FROM Clientes CL WHERE CD.Entidade = CL.Cliente)        WHEN CD.TipoEntidade = 'D' THEN (SELECT TOP 1 Nome FROM OutrosTerceiros OT WHERE CD.Entidade = OT.Terceiro)        ELSE NULL END) NomeEntidade,         V.Comissao, V.ComissaoLiquidacao PercComissaoLiquidacao,         CD.ID, CD.Filial, CD.TipoDoc, CD.Serie, CD.NumDoc, CD.Data,         CD.Entidade, CD.Moeda, CD.Cambio, CD.CambioMBase, CD.CambioMAlt,         LD.Comissao,         SUM(LD.PrecoLiquido) TotalLiquido  FROM CabecDoc CD  INNER JOIN LinhasDoc LD ON CD.ID = LD.IDCabecDoc  INNER JOIN Vendedores V ON LD.Vendedor = V.Vendedor  WHERE CD.TipoDoc IN ('DV','FA','FAI','FI','FM','FO','NC','VD')   AND LD.Data >= '7/1/2010' AND LD.Data <= '8/1/2010'    AND CD.Serie IN ( 'A', 'C_IVA')   AND LD.Vendedor >= '2' AND LD.Vendedor <= '2'  GROUP BY LD.Vendedor, V.Nome, V.Comissao, V.ComissaoLiquidacao, CD.ID,           CD.Filial , CD.TipoDoc, CD.Serie, CD.NumDoc, CD.Data, CD.TipoEntidade,           CD.Entidade, CD.Moeda, CD.Cambio, CD.CambioMBase, CD.CambioMAlt,           LD.Comissao
            //SELECT H.Filial, H.TipoDoc, H.Serie, H.NumDoc, H.DataDoc, H.Entidade, (CASE WHEN H.TipoEntidade = 'C' THEN (SELECT TOP 1 Nome FROM Clientes CL WHERE H.Entidade = CL.Cliente)       WHEN H.TipoEntidade = 'D' THEN (SELECT TOP 1 Nome FROM OutrosTerceiros OT WHERE H.Entidade = OT.Terceiro)      ELSE NULL END) NomeEntidade,  H.ValorTotal, H.TotalIva, H.Vendedor, H.Cambio, H.CambioMBase, H.CambioMAlt, H.NumPrestacao, H.Moeda,         V.Nome, V.Comissao, V.ComissaoLiquidacao PercComissaoLiquidacao, D.TipoDocumento FROM Historico H  INNER JOIN Vendedores V ON H.Vendedor = V.Vendedor INNER JOIN DocumentosCCT D ON H.TipoDoc = D.Documento  WHERE H.DataDoc >= '7/1/2010' AND H.DataDoc <= '8/1/2010'    AND H.TipoDoc IN ('DV','FA','FAI','FI','FM','FO','NC','VD')   AND H.Modulo = 'M'    AND H.TipoEntidade = 'C'    AND H.Serie IN ( 'A', 'C_IVA')   AND H.Vendedor >= '2' AND H.Vendedor <= '2' ORDER BY H.Filial, H.TipoDoc, H.Serie, H.NumDoc

            //SELECT COUNT(1) NReg FROM Vendedores WHERE (Vendedor >= '2' AND Vendedor <= '2') OR Vendedor IN (SELECT Vendedor FROM ChefeVendedores WHERE ChefeVendedores >= '2' AND ChefeVendedores <= '2') AND ComissaoLiquidacao <> 0            

            //SELECT DISTINCT LL.ModuloOrig, LL.FilialOrig, LL.TipoDocOrig, LL.SerieOrig, LL.NumDocOrig, LL.NumPrestacao, LL.NumTransferencia, LL.DataDoc,         LL.ValorRec, CL.Moeda, CL.CambioMBase, CL.CambioMAlt, LL.CambioDocOrig, LL.CambioMBaseDocOrig, LL.CambioMAltDocOrig, CL.Cambio, LL.ValorRecMoedaOrig, CL.ValorDesconto,         LL.TipoDoc, LL.NumDoc, LL.ValorDesconto Desconto,         LL.TipoEntidade, LL.Entidade FROM LinhasLiq LL  INNER JOIN CabLiq CL ON LL.Filial = CL.Filial AND LL.TipoDoc = CL.TipoDoc AND LL.Serie = CL.Serie AND LL.NumDoc = CL.NumDoc INNER JOIN DocumentosCCT DCC ON DCC.Documento = LL.TipoDoc  INNER JOIN CabecDoc CD ON CD.TipoDoc = LL.TipoDocOrig AND CD.Serie = LL.SerieOrig                        AND CD.NumDoc = LL.NumDocOrig AND CD.Filial = LL.FilialOrig  LEFT JOIN LinhasDoc LD ON CD.ID = LD.IDCabecDoc  WHERE LL.TipoEntidade = 'C' AND LL.DataDoc >= '7/1/2010' AND LL.DataDoc <= '8/1/2010'    AND LL.ModuloOrig = 'V'    AND LL.TipoDocOrig IN ('DV','FA','FAI','FI','FM','FO','NC','VD')   AND LL.SerieOrig IN ( 'A', 'C_IVA')   AND LL.ValorRec <> 0    AND DCC.TipoDocumento <> 5   AND LD.Vendedor  = '2' UNION ALL  SELECT DISTINCT LL.ModuloOrig, LL.FilialOrig, LL.TipoDocOrig, LL.SerieOrig, LL.NumDocOrig, LL.NumPrestacao, LL.NumTransferencia, LL.DataDoc,         LL.ValorRec, CL.Moeda, CL.CambioMBase, CL.CambioMAlt, LL.CambioDocOrig, LL.CambioMBaseDocOrig, LL.CambioMAltDocOrig, CL.Cambio, LL.ValorRecMoedaOrig, CL.ValorDesconto,         LL.TipoDoc, LL.NumDoc, LL.ValorDesconto Desconto,         LL.TipoEntidade, LL.Entidade FROM LinhasLiq LL  INNER JOIN CabLiq CL ON LL.Filial = CL.Filial AND LL.TipoDoc = CL.TipoDoc AND LL.Serie = CL.Serie AND LL.NumDoc = CL.NumDoc INNER JOIN DocumentosCCT DCC ON DCC.Documento = LL.TipoDoc  INNER JOIN Historico H ON H.Modulo = 'M' AND H.Filial = LL.FilialOrig AND H.Serie = LL.SerieOrig                        AND H.TipoDoc = LL.TipoDocOrig AND H.NumDoc = LL.NumDocOrig                        AND H.NumPrestacao = LL.NumPrestacao AND H.NumTransferencia = LL.NumTransferencia  WHERE LL.TipoEntidade = 'C' AND LL.DataDoc >= '7/1/2010' AND LL.DataDoc <= '8/1/2010'    AND LL.ModuloOrig = 'M'    AND LL.TipoDocOrig IN ('DV','FA','FAI','FI','FM','FO','NC','VD')   AND LL.SerieOrig IN ( 'A', 'C_IVA')   AND LL.ValorRec <> 0    AND DCC.TipoDocumento <> 5   AND H.Vendedor  = '2'

            //SELECT Vendedor, Nome FROM Vendedores WHERE Vendedor>='2' AND Vendedor<='2'

            //SELECT CV.ChefeVendedores, CV.Vendedor, V.Nome, CV.Comissao  FROM ChefeVendedores CV  INNER JOIN Vendedores V ON CV.Vendedor = V.Vendedor  WHERE ChefeVendedores='2'

            //SELECT SUM(ComVenda + ComLiq) As TotalComissoes FROM ##TmpComissao00 WHERE ID = '{A1055FEB-938E-11DF-9F43-001D7DA9A2CD}' AND Vendedor = '2'

            //INSERT INTO ##TmpComissao00 ([ID], [Posto], [Vendedor], [Nome], [Natureza]) SELECT TOP 1 CF.ID, CF.Posto, CF.Chefe, CF.NomeChefe, C.Natureza FROM ##TmpComissaoChefes00 CF INNER JOIN ##TmpComissao00 C ON C.ID = CF.ID WHERE NOT EXISTS (SELECT Vendedor FROM ##TmpComissao00 WHERE Chefe = Vendedor)

            // SELECT "Vendedores"."Vendedor", "TmpComissao"."Data", "TmpComissao"."TipoDoc", "TmpComissao"."NumDoc", "TmpComissao"."Total", "TmpComissao"."VPago", "TmpComissao"."ComLiq", "TmpComissao"."ComVenda", "TmpComissao"."Natureza", "Vendedores"."ComissaoLiquidacao", "TmpComissao"."Entidade", "TmpComissao"."NumPrestacao", "TmpComissao"."Serie", "TmpComissao"."Vendedor", "TmpComissao"."Posto", "Vendedores"."Nome" FROM   "tempdb"."dbo"."##TmpComissao00" "TmpComissao" INNER JOIN "Vendedores" "Vendedores" ON "TmpComissao"."Vendedor"="Vendedores"."Vendedor" WHERE  "TmpComissao"."Posto"=N'0' AND ("TmpComissao"."Vendedor">=N'2' AND "TmpComissao"."Vendedor"<=N'2 ') ORDER BY "Vendedores"."Vendedor", "TmpComissao"."Vendedor", "TmpComissao"."TipoDoc", "TmpComissao"."Natureza" DESC



        }

        private static string GetPrinterName()
        {
            var printerName = ConfigurationManager.AppSettings["Impressora"];
            if (!string.IsNullOrEmpty(printerName))
                return printerName;
            var printManager = new PrintManager();
            return printManager.GetDefaultPrinterName();
        }

        private static void PrintReport(bool preview)
        {
            var printManager = new PrintManager
            {
                NumberOfCopies = 1,
                PrinterName = GetPrinterName(),
                ReportFile = ConfigurationManager.AppSettings["Relatorio"],
            };
            var user = ConfigurationManager.AppSettings["Utilizador"];
            var password = ConfigurationManager.AppSettings["Senha"];
            var server = ConfigurationManager.AppSettings["FonteDados"];
            var database = ConfigurationManager.AppSettings["NomeBaseDados"];
            if (preview)
                printManager.PrintPreviewDatabaseLogon(user, password, server, database);                
            else
                printManager.PrintDatabaseLogon(user, password, server, database);
        }

       

        public bool IsConnectionValid()
        {
            var userName = ConfigurationManager.AppSettings["Utilizador"];
            var password = ConfigurationManager.AppSettings["Senha"];
            var dataSource = ConfigurationManager.AppSettings["FonteDados"];
            var databaseName = ConfigurationManager.AppSettings["NomeBaseDados"];
            var connectionString = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};MultipleActiveResultSets=True;", dataSource, databaseName, userName, password);
            try
            {
                _connection = new SqlConnection(connectionString);
                _connection.Open();
            }
            catch (Exception e)
            {
                ExceptionPublisher.PublishAndShowErrorMessage(null, e, LogFile, "SalesmanCommissionReport", "Utilizador sem acesso à base de dados.");
                return false;
            }
            return true;
        }

        public void ShowReport()
        {
            var filterXtraForm = new FilterXtraForm { SalesmanData = GetSalesmans() };
            if (filterXtraForm.ShowDialog() == DialogResult.Cancel)
                return;
            SetReportData(filterXtraForm.PeriodBegin, filterXtraForm.PeriodEnd, filterXtraForm.Salesman);             
            PrintReport(filterXtraForm.Preview);
        }
    }
}
